package jonggyuni.service;

import jonggyuni.interfaces.SungJukService;
import jonggyuni.model.SungJukVO;
import java.sql.*;

public class JDBCSungJuk implements SungJukService {
	
	//JDBC 접속 정보
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	
	
	//SQL문 작성
	
	private static final String insertSQL=	"insert into sungjuk(sjno, name, kor, eng, mat)"+
					"VALUES (SJNO.nextval, ?,?,?,?)";//값을 입력 받기 위해서 전부다 ?처리
	
	private static final String selectSQL="select * from sungjuk " + " order by sjno desc";
	//sql문 쓸때 앞뒤로 빈칸을 꼭 만들자. 빈칸을 만들지 않으면 from sungjukorder by sjno 가 되어버려서 에러발생.
	
	private static final String updateSQL=" update sungjuk set name, kor, eng, mat where sjno = ?";

	private static final String deleteSQL=" delete from sungjuk where sjno = ?";//고정된 값이 아니기 때문에 ?를 사용
	

	@Override
	public boolean newSungJuk(SungJukVO sj) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean result = false;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
			//? 매개변수에 들어갈 실제 값을 지정
			ps.setString(1, sj.getName());//숫자,문자 상관없이 setString, parameterIndext에는 순서
			ps.setInt(2, sj.getKor());
			ps.setInt(3, sj.getEng());
			ps.setInt(4, sj.getMath());
		
			int cnt = ps.executeUpdate();
			//성공적으로 insert되면 result를 true로 
			if(cnt > 0) result = true;
		}catch(Exception ex){
			ex.printStackTrace();			
		}finally{
			if(ps!=null)
				try {ps.close();} catch(Exception e){}
			if(conn!=null)
				try {conn.close();} catch(Exception e){}
		}
		
		return false;
	}

	@Override
	public String readSungJuk() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			
			//selectSQL 실행결과를 모두 출력
			while(rs.next()){
				for(int i=1; i<=9; ++i){
					result += rs.getString(i)+ " ";
				}
				result += "\n";
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{//순서대로 닫지 않으면 중간에 메모리가 둥둥 떠다니는 현상의 메모리 낭비가 생김
			if(rs!=null)
				try{rs.close();} catch(Exception e){}
			if(ps!=null)
				try{ps.close();} catch(Exception e){}
			if(conn!=null)
				try{conn.close();} catch(Exception e){}
		}
		return result;
	}

	@Override
	public boolean modifySungJuk(SungJukVO sj) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean removeSugnJuk(String key) {
		// TODO Auto-generated method stub
		return false;
	}

}
